﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FortuneHeavenCommon.DataMap;
using System.Data.SqlClient;
using FortuneHeavenCommon.ComMethod;
using System.Data;

namespace FortuneHeavan.Game.DAL
{
    public class AccountsInfoDAL : UserDBBase
    {
        public AccountsInfo CheckUserPwd(string user, string password)
        {
            string sql = @"select UserID, Accounts,GameID 
from AccountsInfo where Accounts =@Accounts and LogonPass =@LogonPass";
            List<AccountsInfo> list = Query<AccountsInfo>(sql, new SqlParameter("@Accounts", user), new SqlParameter("@LogonPass", password));
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }

        public void SetNewPassword(int userid,string password)
        {
            string sql = "Update AccountsInfo set LogonPass =@password where UserID =@userid";
            RunSQLNonQuery(sql, new SqlParameter("@password", password), new SqlParameter("@userid", userid));
        }

        public bool CheckExist(string user)
        {
            string sql = @"select UserID, Accounts
from AccountsInfo where Accounts =@Accounts";
            List<AccountsInfo> list = Query<AccountsInfo>(sql, new SqlParameter("@Accounts", user));
            if (list != null && list.Count > 0)
            {
                return true;
            }
            return false;
        }

        public AccountsInfo GetLightAcountInfor(string user)
        {
            string sql = @"select UserID, Accounts,Gender,GameID,MemberOrder,MemberOverDate,SpreaderID 
from AccountsInfo where Accounts =@Accounts";
            List<AccountsInfo> list = Query<AccountsInfo>(sql, new SqlParameter("@Accounts", user));
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }

        public List<AccountsInfo> GetSpreadList(int page, int size, int SpreaderID)
        {
            string sql =
            @"SELECT * FROM ( select UserID, Accounts,Gender,GameID,MemberOrder,MemberOverDate,RegisterDate ,ROW_NUMBER() OVER(ORDER BY UserID DESC) ROW from AccountsInfo where SpreaderID =@SpreaderID) FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";
            List<AccountsInfo> list = Query<AccountsInfo>(sql, new SqlParameter("@SpreaderID", SpreaderID),new SqlParameter("@p0", size * (page - 1) + 1),new SqlParameter("@p1", size * page));
            return list;
        }

        public int GetSpreadTotalSize(int SpreaderID)
        {
            string sql = "select count(UserID) from AccountsInfo where SpreaderID =@SpreaderID";
            return RunSQL(sql, new SqlParameter("@SpreaderID", SpreaderID));
        }

        public void UpdateGender(int gender,int userid)
        {
            string sql = "Update AccountsInfo set Gender=@gender where UserID = @userid";
            RunSQLNonQuery(sql, new SqlParameter("@gender", gender), new SqlParameter("@userid", userid));
        }

        /// <summary>
        /// 获取游戏总局最多的
        /// </summary>
        /// <param name="top"></param>
        public List<AccountsInfo> GetTopShortInfor(int top)
        {
            string sql = @"Select TOP " + top + @" UserID,Accounts,GameID,FaceID,Gender,Experience,Loveliness from dbo.AccountsInfo ORDER BY Loveliness DESC";
            return Query<AccountsInfo>(sql);
        }

        public List<AccountsInfo> GetUserInfor(int page, int size, string username,string spread)
        {
            string condition = "where 1=1 ";
            List<SqlParameter> content = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(username))
            {
                condition += " and Accounts =@username";
                content.Add(new SqlParameter("@username", username));
            }
            if (!string.IsNullOrEmpty(spread))
            {
                condition += " and SpreaderID in (select UserId FROM AccountsInfo where Accounts =@spread)";
                content.Add(new SqlParameter("@spread", spread));
            }
          
            content.Add(new SqlParameter("@p0", size * (page - 1) + 1));
            content.Add(new SqlParameter("@p1", size * page));
            string sql = @"SELECT * FROM ( select *,ROW_NUMBER() OVER(ORDER BY UserID DESC) ROW from AccountsInfo {0}) FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";
            List<AccountsInfo> list = Query<AccountsInfo>(string.Format(sql, condition), content.ToArray());
            return list;
        }

        public int GetTotalSize(string username,string spread)
        {
            string condition = "where 1=1 ";
            List<SqlParameter> content = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(username))
            {
                condition += " and Accounts =@username";
                content.Add(new SqlParameter("@username", username));
            }
            if (!string.IsNullOrEmpty(spread))
            {
                condition += " and SpreaderID in (select UserId FROM AccountsInfo where Accounts =@spread)";
                content.Add(new SqlParameter("@spread", spread));
            }
            string sql = @"select count(UserID) from AccountsInfo {0}";
            return RunSQL(string.Format(sql, condition), content.ToArray());
        }

        public void Delete(List<int> userids)
        {
            if(userids ==null ||userids.Count ==0)
                return;
            string condition ="";
            foreach(int k in userids)
            {
                condition= condition + k +",";
            }
           condition= condition.TrimEnd(',');
            string sql = "Delete from AccountsInfo where UserID in({0})";
            RunSQLNonQuery(string.Format(sql, condition));
        }

        public void Start(List<int> userids)
        {
            if (userids == null || userids.Count == 0)
                return;
            string condition = "";
            foreach (int k in userids)
            {
                condition = condition + k + ",";
            }
           condition= condition.TrimEnd(',');;
            string sql = "Update AccountsInfo Set Nullity = 0 WHERE UserID in({0})";
            RunSQLNonQuery(string.Format(sql, condition));
        }

        public void Stop(List<int> userids)
        {
            if (userids == null || userids.Count == 0)
                return;
            string condition = "";
            foreach (int k in userids)
            {
                condition = condition + k + ",";
            }
           condition=  condition.TrimEnd(','); ;
            string sql = "Update AccountsInfo Set Nullity = 1 WHERE UserID in({0})";
            RunSQLNonQuery(string.Format(sql, condition));
        }
        
        public AccountsInfo GetAcount(int id)
        {
            string sql = "Select * from AccountsInfo where UserID =@UserID";
            List<AccountsInfo> content = Query<AccountsInfo>(sql, new SqlParameter("@UserID",id));
            if (content != null && content.Count > 0)
            {
                return content[0];
            }
            return null;
        }

        public AccountsInfo GetAcount(string name)
        {
            string sql = "Select * from AccountsInfo where Accounts =@Accounts";
            List<AccountsInfo> content = Query<AccountsInfo>(sql, new SqlParameter("@Accounts", name));
            if (content != null && content.Count > 0)
            {
                return content[0];
            }
            return null;
        }

        public List<AccountsInfo> GetMemberUserInfor(int page, int size, string username,int type)
        {
            string condition = "where MemberOrder > 0 ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(username))
            {
                condition += " and Accounts =@username";
                content.Add(new SqlParameter("@username", username));
            }

            if (type >= 1 && type <= 4)
            {
                condition += " and MemberOrder =@MemberOrder";
                content.Add(new SqlParameter("@MemberOrder", type));
            }
            

            content.Add(new SqlParameter("@p0", size * (page - 1) + 1));
            content.Add(new SqlParameter("@p1", size * page));
            string sql = @"SELECT * FROM ( select *,ROW_NUMBER() OVER(ORDER BY UserID DESC) ROW from AccountsInfo {0}) FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";
            List<AccountsInfo> list = Query<AccountsInfo>(string.Format(sql, condition), content.ToArray());
            return list;
        }

        public int GetMemberTotalSize(string username, int type)
        {
            string condition = "where MemberOrder > 0 ";
            List<SqlParameter> content = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(username))
            {
                condition += " and Accounts =@username";
                content.Add(new SqlParameter("@username", username));
            }

            if (type >= 1 && type <= 4)
            {
                condition += " and MemberOrder =@MemberOrder";
                content.Add(new SqlParameter("@MemberOrder", type));
            }

            string sql = @"select count(UserID) from AccountsInfo {0}";
            return RunSQL(string.Format(sql, condition), content.ToArray());
        }

        public bool RegisterNameAcount(AccountsInfo infor,ref string error)
        {
            try
            {
                int k = 0;
                SqlConnection conn = new SqlConnection(ConnectionString);
                conn.Open();
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "GSP_GP_RegisterAccountsEx";
                command.CommandType = System.Data.CommandType.StoredProcedure;


                command.Parameters.Add(new SqlParameter("@strAccounts", infor.Accounts));
                command.Parameters.Add(new SqlParameter("@strPassword", infor.LogonPass));
                command.Parameters.Add(new SqlParameter("@strSpreader", infor.SpreadName));
                command.Parameters.Add(new SqlParameter("@wFaceID", infor.FaceID));
                command.Parameters.Add(new SqlParameter("@cbGender", infor.Gender));
                command.Parameters.Add(new SqlParameter("@strClientIP", infor.RegisterIP));
                command.Parameters.Add(new SqlParameter("@strMachineSerial", infor.MachineSerial));
                command.Parameters.Add(new SqlParameter("@strRealName", infor.Add_RealName));

                command.Parameters.Add(new SqlParameter("@strIDNumber", infor.Add_IDNumber));
                command.Parameters.Add(new SqlParameter("@strArea", infor.Add_Area));
                command.Parameters.Add(new SqlParameter("@strBankName", infor.Add_BankName));
                command.Parameters.Add(new SqlParameter("@strBankID", infor.Add_BankID));
                command.Parameters.Add(new SqlParameter("@strMobilePhone", infor.Add_MobilePhone));
                command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int errorindex = reader.GetOrdinal("ErrorDescribe");
                    if (!reader.IsDBNull(errorindex))
                    {
                        error = reader.GetString(errorindex);
                    }
                    break;
                }
                reader.Close();
                if ((int)command.Parameters["@RETURN_VALUE"].Value != 0)
                {
                    //command.Dispose();

                    conn.Close();
                    return false;
                }
                //command.Dispose();
                conn.Close();
                return true;
            }
            catch(Exception exp)
            {
                error = "注册失败.";
                return false;
            }
        }
    }
}
